package com.ruoyi.candidate.mapper;

import com.ruoyi.candidate.domain.Candidate;
import com.ruoyi.common.core.domain.entity.SysUser;
import org.apache.ibatis.annotations.*;

import java.util.List;

public interface CandidateMapper {

    /**
     * 根据身份证号查询考生信息及对应的专升本考试报名信息，
     * 将两表数据合并后封装至 CandidateExamInfo 对象中返回。
     *
     * @param idNumber 身份证号
     * @return CandidateExamInfo 合并后的考生及考试报名信息
     */
    @Select("SELECT " +
            "c.id AS candidateId, c.id_number, c.name, c.gender, c.birth_date, c.phone, c.password, " +
            "c.ethnicity_id, c.political_status_id, c.foreign_language_id, " +
            "r.id AS registrationId, r.vocational_college_id, r.vocational_major_id, r.candidate_type_id, " +
            "r.address, r.is_certificate_issued, r.recipient_name, r.postal_code, " +
            "r.exam_center_city_id, r.exam_major_id, r.volunteer_college_id, r.volunteer_major_id " +
            "FROM tb_candidate c " +
            "LEFT JOIN tb_zsb_exam_registration r ON c.id_number = r.id_number " +
            "WHERE c.id_number = #{idNumber}")
    Candidate selectCandidateByIdNumber(@Param("idNumber") String idNumber);

    /**
     * 根据身份证号和密码查询考生信息及对应的专升本考试报名信息，
     * 用于登录验证，将两表数据合并后封装至 CandidateExamInfo 对象中返回。
     *
     * @param idNumber 身份证号
     * @param password 登录密码
     * @return CandidateExamInfo 合并后的考生及考试报名信息
     */
    @Select("SELECT " +
            "c.id AS candidateId, c.id, c.id_number, c.name, c.gender, c.birth_date, c.phone, c.password, " +
            "c.ethnicity_id, c.political_status_id, c.foreign_language_id, " +
            "r.id AS registrationId, r.vocational_college_id, r.vocational_major_id, r.candidate_type_id, " +
            "r.address, r.is_certificate_issued, r.recipient_name, r.postal_code, " +
            "r.exam_center_city_id, r.exam_major_id, r.volunteer_college_id, r.volunteer_major_id " +
            "FROM tb_candidate c " +
            "LEFT JOIN tb_zsb_exam_registration r ON c.id_number = r.id_number " +
            "WHERE c.id_number = #{idNumber} " +
            "AND c.password = #{password}")
    Candidate selectCandidateForLogin(@Param("idNumber") String idNumber, @Param("password") String password);

    // 插入新的考生信息
    @Insert("INSERT INTO tb_candidate (id_number, name, gender, birth_date," +
            " ethnicity_id, political_status_id, foreign_language_id, phone, password) " +
            "VALUES (#{id_number}, #{name}, #{gender}, #{birth_date}," +
            " #{ethnicity_id}, #{political_status_id}, #{foreign_language_id}, #{phone}, #{password})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insertCandidate(Candidate candidate);


    // 更新考生的密码
    @Update("UPDATE tb_candidate SET password = #{newPassword} WHERE id_number = #{idNumber} AND phone = #{phone}")
    int updatePassword(@Param("idNumber") String idNumber, @Param("phone") String phone, @Param("newPassword") String newPassword);

    /**
     * 更新或插入考生的报名相关信息。
     * 如果报名表中存在该身份证号的记录则执行更新，否则执行插入操作。
     *
     * @param candidate 包含报名相关信息的对象
     * @return 受影响的记录数
     */
    @Insert("INSERT INTO tb_zsb_exam_registration(" +
            "id_number, vocational_college_id, vocational_major_id, candidate_type_id, " +
            "exam_center_city_id, exam_major_id, volunteer_college_id, volunteer_major_id" +
            ") VALUES (" +
            "#{id_number}, #{vocational_college_id}, #{vocational_major_id}, #{candidate_type_id}, " +
            "#{exam_center_city_id}, #{exam_major_id}, #{volunteer_college_id}, #{volunteer_major_id}" +
            ") ON DUPLICATE KEY UPDATE " +
            "vocational_college_id = VALUES(vocational_college_id), " +
            "vocational_major_id = VALUES(vocational_major_id), " +
            "candidate_type_id = VALUES(candidate_type_id), " +
            "exam_center_city_id = VALUES(exam_center_city_id), " +
            "exam_major_id = VALUES(exam_major_id), " +
            "volunteer_college_id = VALUES(volunteer_college_id), " +
            "volunteer_major_id = VALUES(volunteer_major_id)")
    int updateRegistrationInfo(Candidate candidate);


    // 更新考生的基本信息
    @Update("UPDATE tb_candidate SET name = #{name}, gender = #{gender}, " +
            "birth_date = #{birth_date}, ethnicity_id = #{ethnicity_id}, " +
            "political_status_id = #{political_status_id}, foreign_language_id = #{foreign_language_id}," +
            " phone = #{phone} " +
            "WHERE id_number = #{id_number}")
    int updateBasicInfo(Candidate candidate);

    // 更新或添加考生的收取通知书信息
    @Update("UPDATE tb_zsb_exam_registration SET address = #{address}," +
            " postal_code = #{postal_code}, recipient_name = #{recipient_name} " +
            ", is_certificate_issued = #{is_certificate_issued} WHERE id_number = #{id_number}")
    int updateNotificationInfo(Candidate candidate);


    /**
     * 根据考生编号查询考生信息及对应的专升本考试报名信息，
     * 将两表数据合并后封装至 CandidateExamInfo 对象中返回。
     *
     * @param id 考生编号（tb_candidate 表的主键）
     * @return CandidateExamInfo 合并后的考生及考试报名信息
     */
    @Select("SELECT " +
            "c.id AS candidateId, c.id_number, c.name, c.gender, c.birth_date, c.phone, c.password, " +
            "c.ethnicity_id, c.political_status_id, c.foreign_language_id, " +
            "r.id AS registrationId, r.vocational_college_id, r.vocational_major_id, r.candidate_type_id, " +
            "r.address, r.is_certificate_issued, r.recipient_name, r.postal_code, " +
            "r.exam_center_city_id, r.exam_major_id, r.volunteer_college_id, r.volunteer_major_id " +
            "FROM tb_candidate c " +
            "LEFT JOIN tb_zsb_exam_registration r ON c.id_number = r.id_number " +
            "WHERE c.id = #{id}")
    Candidate selectCandidateById(@Param("id") Long id);

    @Select("SELECT * from tb_candidate where id = #{id}")
    Candidate selectBaseInfoById(@Param("id") Long id);

    // 根据考生编号删除考生信息
    @Delete("DELETE FROM tb_candidate WHERE id = #{id}")
    int deleteCandidateById(@Param("id") Long id);
}
